package demo3;

import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.function.BiPredicate;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DBManager {
    private final Map<String, Table> dataDictionary;
    private final Map<String, Map<String, Map<String, String>>> tableData;
    private final Map<String, BiPredicate<Integer, Integer>> operators;
    private static final String DICT_FILE = "dataDictionary.txt";

    // SQL匹配正则（静态常量）
    private static final Pattern PATTERN_CREATE = Pattern.compile(
            "CREATE\\s+TABLE\\s+([A-Za-z_][A-Za-z0-9_]*)\\s*\\(([^;]*)\\)\\s*;?",
            Pattern.CASE_INSENSITIVE
    );
    private static final Pattern PATTERN_INSERT_WITH_COLS = Pattern.compile(
            "INSERT\\s+INTO\\s+([A-Za-z_][A-Za-z0-9_]*)\\s*\\(([^)]*)\\)\\s+VALUES\\s*\\(([^)]*)\\)\\s*;?",
            Pattern.CASE_INSENSITIVE
    );
    private static final Pattern PATTERN_INSERT_NO_COLS = Pattern.compile(
            "INSERT\\s+INTO\\s+([A-Za-z_][A-Za-z0-9_]*)\\s+VALUES\\s*\\(([^)]*)\\)\\s*;?",
            Pattern.CASE_INSENSITIVE
    );
    private static final Pattern PATTERN_DELETE = Pattern.compile(
            "DELETE\\s+FROM\\s+([A-Za-z_][A-Za-z0-9_]*)\\s*(WHERE\\s+(.*))?\\s*;?",
            Pattern.CASE_INSENSITIVE
    );
    private static final Pattern PATTERN_UPDATE = Pattern.compile(
            "UPDATE\\s+([A-Za-z_][A-Za-z0-9_]*)\\s+SET\\s+([^;]*)\\s*(WHERE\\s+(.*))?\\s*;?",
            Pattern.CASE_INSENSITIVE
    );
    private static final Pattern PATTERN_SELECT = Pattern.compile(
            "SELECT\\s+\\*\\s+FROM\\s+([A-Za-z_][A-Za-z0-9_]*)\\s*(WHERE\\s+(.*))?\\s*;?",
            Pattern.CASE_INSENSITIVE
    );
    private static final Pattern PATTERN_DROP = Pattern.compile(
            "DROP\\s+TABLE\\s+([A-Za-z_][A-Za-z0-9_]*)\\s*;?",
            Pattern.CASE_INSENSITIVE
    );
    private static final Pattern PATTERN_CONDITION = Pattern.compile(
            "(\\w+)\\s*(=|>|<|>=|<=|!=)\\s*([^\\s]+)",
            Pattern.CASE_INSENSITIVE
    );
    private static final Pattern PATTERN_PRIMARY_KEY = Pattern.compile(
            "PRIMARY\\s+KEY\\s*\\(.*\\)",
            Pattern.CASE_INSENSITIVE
    );


    public DBManager() {
        this.dataDictionary = new HashMap<>();
        this.tableData = new HashMap<>();
        this.operators = new HashMap<>();
        initOperators();
        initDataDictionary();
    }


    private void initOperators() {
        operators.put("<", (a, b) -> a < b);
        operators.put(">", (a, b) -> a > b);
        operators.put("<=", (a, b) -> a <= b);
        operators.put(">=", (a, b) -> a >= b);
        operators.put("=", Integer::equals);
        operators.put("!=", (a, b) -> !a.equals(b));
    }


    public void initDataDictionary() {
        File dictFile = new File(DICT_FILE);
        if (!dictFile.exists()) {
            try {
                Files.writeString(Paths.get(DICT_FILE), "# 数据字典初始化\n", StandardCharsets.UTF_8);
                System.out.println("[INFO] 数据字典文件创建成功：" + DICT_FILE);
            } catch (IOException e) {
                System.err.println("[ERROR] 创建数据字典失败：" + e.getMessage());
            }
            return;
        }

        try {
            List<String> lines = Files.readAllLines(Paths.get(DICT_FILE), StandardCharsets.UTF_8);
            for (String line : lines) {
                line = line.trim();
                if (line.isEmpty() || line.startsWith("#")) continue;

                String[] parts = line.split("/");
                if (parts.length < 2) {
                    System.err.println("[WARN] 数据字典行格式错误：" + line);
                    continue;
                }

                String tableName = parts[0].trim();
                List<Column> columns = new ArrayList<>();
                for (int i = 1; i < parts.length; i++) {
                    String colPart = parts[i].trim();
                    String[] colAttrs = colPart.split("\\s+");
                    if (colAttrs.length != 5) {
                        System.err.println("[WARN] 列属性格式错误：" + colPart);
                        continue;
                    }
                    columns.add(new Column(
                            colAttrs[0],
                            colAttrs[1],
                            Boolean.parseBoolean(colAttrs[2]),
                            Boolean.parseBoolean(colAttrs[3]),
                            Boolean.parseBoolean(colAttrs[4])
                    ));
                }
                dataDictionary.put(tableName, new Table(tableName, columns));
            }
            System.out.println("[INFO] 数据字典加载完成，共" + dataDictionary.size() + "张表");
        } catch (IOException e) {
            System.err.println("[ERROR] 读取数据字典失败：" + e.getMessage());
        }
    }


    public void writeDataDictionary() {
        try {
            StringBuilder content = new StringBuilder("# 数据字典（自动生成）\n");
            for (Map.Entry<String, Table> entry : dataDictionary.entrySet()) {
                String tableName = entry.getKey();
                Table table = entry.getValue();
                content.append(tableName);

                for (Column col : table.getColumns()) {
                    content.append("/")
                            .append(col.getName()).append(" ")
                            .append(col.getType()).append(" ")
                            .append(col.isPrimaryKey()).append(" ")
                            .append(col.isNullable()).append(" ")
                            .append(col.isUnique());
                }
                content.append("\n");
            }
            Files.writeString(Paths.get(DICT_FILE), content.toString(), StandardCharsets.UTF_8);
        } catch (IOException e) {
            System.err.println("[ERROR] 写入数据字典失败：" + e.getMessage());
        }
    }


    public void createTable(String sql) {
        Matcher matcher = PATTERN_CREATE.matcher(sql);
        if (!matcher.find()) {
            System.err.println("[ERROR] CREATE TABLE格式错误！示例：CREATE TABLE student(sid INT, name VARCHAR(20), PRIMARY KEY(sid));");
            return;
        }

        String tableName = matcher.group(1).trim();
        if (dataDictionary.containsKey(tableName)) {
            System.err.println("[ERROR] 表'" + tableName + "'已存在");
            return;
        }

        String colsStr = matcher.group(2).trim();
        String[] colParts = colsStr.split(",");
        List<Column> columns = new ArrayList<>();
        String primaryKey = "";

        for (String part : colParts) {
            part = part.trim();
            if (part.isEmpty()) continue;

            // 修复：使用Pattern匹配主键，避免String.matches()多参数错误
            if (PATTERN_PRIMARY_KEY.matcher(part).matches()) {
                Matcher pkMatcher = Pattern.compile("PRIMARY\\s+KEY\\s*\\(([^)]+)\\)", Pattern.CASE_INSENSITIVE).matcher(part);
                if (pkMatcher.find()) {
                    primaryKey = pkMatcher.group(1).trim();
                }
                continue;
            }

            String[] colAttrs = part.split("\\s+");
            if (colAttrs.length < 2) {
                System.err.println("[WARN] 列定义格式错误：" + part);
                continue;
            }
            columns.add(new Column(
                    colAttrs[0].trim(),
                    colAttrs[1].trim(),
                    false,
                    true,
                    false
            ));
        }

        for (Column col : columns) {
            if (col.getName().equals(primaryKey)) {
                col.setPrimaryKey(true);
                col.setNullable(false);
                col.setUnique(true);
                break;
            }
        }

        dataDictionary.put(tableName, new Table(tableName, columns));
        if (createTableDataFile(tableName)) {
            System.out.println("[SUCCESS] 表'" + tableName + "'创建成功");
        }
    }


    private boolean createTableDataFile(String tableName) {
        try {
            Files.createFile(Paths.get(tableName + ".txt"));
            return true;
        } catch (IOException e) {
            System.err.println("[ERROR] 创建表文件失败：" + e.getMessage());
            return false;
        }
    }


    public void insertInto(String sql) {
        Matcher matcherWithCols = PATTERN_INSERT_WITH_COLS.matcher(sql);
        if (matcherWithCols.find()) {
            insertWithColumns(matcherWithCols);
            return;
        }

        Matcher matcherNoCols = PATTERN_INSERT_NO_COLS.matcher(sql);
        if (matcherNoCols.find()) {
            insertWithoutColumns(matcherNoCols);
            return;
        }

        System.err.println("[ERROR] INSERT格式错误！示例：INSERT INTO student(sid,name) VALUES(1,'张三');");
    }


    private void insertWithColumns(Matcher matcher) {
        String tableName = matcher.group(1).trim();
        String colsStr = matcher.group(2).trim();
        String valsStr = removeQuotes(matcher.group(3).trim());

        if (!dataDictionary.containsKey(tableName)) {
            System.err.println("[ERROR] 表'" + tableName + "'不存在");
            return;
        }

        String[] cols = Arrays.stream(colsStr.split(","))
                .map(String::trim)
                .filter(s -> !s.isEmpty())
                .toArray(String[]::new);
        String[] vals = Arrays.stream(valsStr.split(","))
                .map(String::trim)
                .filter(s -> !s.isEmpty())
                .toArray(String[]::new);

        if (cols.length != vals.length) {
            System.err.println("[ERROR] 列数与值数不匹配");
            return;
        }

        String pkName = findPrimaryKey(tableName);
        if (pkName.isEmpty()) {
            System.err.println("[ERROR] 表未设置主键");
            return;
        }

        String pkValue = "";
        for (int i = 0; i < cols.length; i++) {
            if (cols[i].equals(pkName)) {
                pkValue = vals[i];
                break;
            }
        }
        if (pkValue.isEmpty()) {
            System.err.println("[ERROR] 未指定主键'" + pkName + "'");
            return;
        }

        tableData.computeIfAbsent(tableName, k -> new HashMap<>());
        Map<String, Map<String, String>> colDataMap = tableData.get(tableName);
        for (int i = 0; i < cols.length; i++) {
            colDataMap.computeIfAbsent(cols[i], k -> new HashMap<>()).put(pkValue, vals[i]);
        }

        if (writeTableData(tableName)) {
            System.out.println("[SUCCESS] 插入成功，主键：" + pkValue);
        }
    }


    private void insertWithoutColumns(Matcher matcher) {
        String tableName = matcher.group(1).trim();
        String valsStr = removeQuotes(matcher.group(2).trim());
        String[] vals = Arrays.stream(valsStr.split(","))
                .map(String::trim)
                .filter(s -> !s.isEmpty())
                .toArray(String[]::new);

        Table table = dataDictionary.get(tableName);
        if (table == null || table.getColumns().size() != vals.length) {
            System.err.println("[ERROR] 列数与值数不匹配");
            return;
        }

        String pkName = findPrimaryKey(tableName);
        String pkValue = "";
        for (int i = 0; i < table.getColumns().size(); i++) {
            if (table.getColumns().get(i).isPrimaryKey()) {
                pkValue = vals[i];
                break;
            }
        }
        if (pkValue.isEmpty()) {
            System.err.println("[ERROR] 表未设置主键");
            return;
        }

        tableData.computeIfAbsent(tableName, k -> new HashMap<>());
        Map<String, Map<String, String>> colDataMap = tableData.get(tableName);
        for (int i = 0; i < table.getColumns().size(); i++) {
            String colName = table.getColumns().get(i).getName();
            colDataMap.computeIfAbsent(colName, k -> new HashMap<>()).put(pkValue, vals[i]);
        }

        if (writeTableData(tableName)) {
            System.out.println("[SUCCESS] 插入成功，主键：" + pkValue);
        }
    }


    public void readTableData(String tableName) {
        tableData.remove(tableName);
        Map<String, Map<String, String>> colDataMap = new HashMap<>();
        tableData.put(tableName, colDataMap);

        Table table = dataDictionary.get(tableName);
        if (table == null) return;

        String pkName = findPrimaryKey(tableName);
        if (pkName.isEmpty()) return;

        try {
            List<String> lines = Files.readAllLines(Paths.get(tableName + ".txt"), StandardCharsets.UTF_8);
            for (String line : lines) {
                line = line.trim();
                if (line.isEmpty()) continue;

                String[] vals = line.split("\\s+");
                if (vals.length != table.getColumns().size()) continue;

                String pkValue = "";
                for (int i = 0; i < table.getColumns().size(); i++) {
                    if (table.getColumns().get(i).getName().equals(pkName)) {
                        pkValue = vals[i];
                        break;
                    }
                }

                for (int i = 0; i < table.getColumns().size(); i++) {
                    String colName = table.getColumns().get(i).getName();
                    colDataMap.computeIfAbsent(colName, k -> new HashMap<>()).put(pkValue, vals[i]);
                }
            }
        } catch (IOException e) {
            System.err.println("[ERROR] 读取表数据失败：" + e.getMessage());
        }
    }


    private boolean writeTableData(String tableName) {
        Table table = dataDictionary.get(tableName);
        if (table == null) return false;

        Map<String, Map<String, String>> colDataMap = tableData.get(tableName);
        if (colDataMap.isEmpty()) return false;

        String pkName = findPrimaryKey(tableName);
        if (pkName.isEmpty() || !colDataMap.containsKey(pkName)) return false;

        try {
            StringBuilder content = new StringBuilder();
            for (String pk : colDataMap.get(pkName).keySet()) {
                StringBuilder line = new StringBuilder();
                for (Column col : table.getColumns()) {
                    line.append(colDataMap.getOrDefault(col.getName(), new HashMap<>()).getOrDefault(pk, "NULL")).append(" ");
                }
                content.append(line.toString().trim()).append("\n");
            }
            Files.writeString(Paths.get(tableName + ".txt"), content.toString(), StandardCharsets.UTF_8);
            return true;
        } catch (IOException e) {
            System.err.println("[ERROR] 写入表数据失败：" + e.getMessage());
            return false;
        }
    }


    public void deleteFrom(String sql) {
        Matcher matcher = PATTERN_DELETE.matcher(sql);
        if (!matcher.find()) {
            System.err.println("[ERROR] DELETE格式错误！示例：DELETE FROM student WHERE sid=1;");
            return;
        }

        String tableName = matcher.group(1).trim();
        String whereCond = matcher.group(3) != null ? matcher.group(3).trim() : "";

        if (!dataDictionary.containsKey(tableName)) {
            System.err.println("[ERROR] 表'" + tableName + "'不存在");
            return;
        }

        readTableData(tableName);
        Map<String, Map<String, String>> colDataMap = tableData.get(tableName);
        if (colDataMap.isEmpty()) {
            System.out.println("[INFO] 表无数据可删");
            return;
        }

        String pkName = findPrimaryKey(tableName);
        Set<String> allPks = new HashSet<>(colDataMap.getOrDefault(pkName, new HashMap<>()).keySet());
        Set<String> deletePks = whereCond.isEmpty() ? allPks : getMatchPks(tableName, whereCond);

        if (deletePks.isEmpty()) {
            System.out.println("[INFO] 无匹配数据可删");
            return;
        }

        for (Map<String, String> colVals : colDataMap.values()) {
            deletePks.forEach(colVals::remove);
        }

        if (writeTableData(tableName)) {
            System.out.println("[SUCCESS] 删除成功，共" + deletePks.size() + "条记录");
        }
    }


    public void update(String sql) {
        Matcher matcher = PATTERN_UPDATE.matcher(sql);
        if (!matcher.find()) {
            System.err.println("[ERROR] UPDATE格式错误！示例：UPDATE student SET name='李四' WHERE sid=1;");
            return;
        }

        String tableName = matcher.group(1).trim();
        String setStr = matcher.group(2).trim();
        String whereCond = matcher.group(4) != null ? matcher.group(4).trim() : "";

        if (!dataDictionary.containsKey(tableName)) {
            System.err.println("[ERROR] 表'" + tableName + "'不存在");
            return;
        }

        Matcher setMatcher = Pattern.compile("([A-Za-z_]+)\\s*=\\s*(.*)", Pattern.CASE_INSENSITIVE).matcher(setStr);
        if (!setMatcher.find()) {
            System.err.println("[ERROR] SET子句格式错误");
            return;
        }
        String colName = setMatcher.group(1).trim();
        String newValue = removeQuotes(setMatcher.group(2).trim());

        readTableData(tableName);
        Map<String, Map<String, String>> colDataMap = tableData.get(tableName);
        if (!colDataMap.containsKey(colName)) {
            System.err.println("[ERROR] 列'" + colName + "'不存在");
            return;
        }

        String pkName = findPrimaryKey(tableName);
        Set<String> targetPks = whereCond.isEmpty()
                ? new HashSet<>(colDataMap.getOrDefault(pkName, new HashMap<>()).keySet())
                : getMatchPks(tableName, whereCond);

        if (targetPks.isEmpty()) {
            System.out.println("[INFO] 无匹配数据可更新");
            return;
        }

        Map<String, String> colVals = colDataMap.get(colName);
        targetPks.forEach(pk -> colVals.put(pk, newValue));

        if (writeTableData(tableName)) {
            System.out.println("[SUCCESS] 更新成功，共" + targetPks.size() + "条记录");
        }
    }


    public void select(String sql) {
        Matcher matcher = PATTERN_SELECT.matcher(sql);
        if (!matcher.find()) {
            System.err.println("[ERROR] SELECT格式错误！示例：SELECT * FROM student WHERE age>18;");
            return;
        }

        String tableName = matcher.group(1).trim();
        String whereCond = matcher.group(3) != null ? matcher.group(3).trim() : "";

        if (!dataDictionary.containsKey(tableName)) {
            System.err.println("[ERROR] 表'" + tableName + "'不存在");
            return;
        }

        readTableData(tableName);
        Map<String, Map<String, String>> colDataMap = tableData.get(tableName);
        if (colDataMap.isEmpty()) {
            System.out.println("[INFO] 表无数据");
            return;
        }

        Table table = dataDictionary.get(tableName);
        String pkName = findPrimaryKey(tableName);
        Set<String> targetPks = whereCond.isEmpty()
                ? new HashSet<>(colDataMap.getOrDefault(pkName, new HashMap<>()).keySet())
                : getMatchPks(tableName, whereCond);

        if (targetPks.isEmpty()) {
            System.out.println("[INFO] 无匹配查询结果");
            return;
        }

        System.out.println("\n===== 表 " + tableName + " 数据 =====");
        StringBuilder header = new StringBuilder();
        for (Column col : table.getColumns()) {
            header.append(String.format("%-15s", col.getName()));
        }
        System.out.println(header);
        System.out.println("-".repeat(header.length()));

        for (String pk : targetPks) {
            StringBuilder row = new StringBuilder();
            for (Column col : table.getColumns()) {
                String val = colDataMap.getOrDefault(col.getName(), new HashMap<>()).getOrDefault(pk, "NULL");
                row.append(String.format("%-15s", val));
            }
            System.out.println(row);
        }
        System.out.println("===== 共" + targetPks.size() + "条记录 =====");
    }


    public void dropTable(String sql) {
        Matcher matcher = PATTERN_DROP.matcher(sql);
        if (!matcher.find()) {
            System.err.println("[ERROR] DROP格式错误！示例：DROP TABLE student;");
            return;
        }

        String tableName = matcher.group(1).trim();
        if (!dataDictionary.containsKey(tableName)) {
            System.err.println("[ERROR] 表'" + tableName + "'不存在");
            return;
        }

        dataDictionary.remove(tableName);
        tableData.remove(tableName);
        try {
            Files.deleteIfExists(Paths.get(tableName + ".txt"));
        } catch (IOException e) {
            System.err.println("[ERROR] 删除表文件失败：" + e.getMessage());
        }
        System.out.println("[SUCCESS] 表'" + tableName + "'删除成功");
    }


    private String removeQuotes(String str) {
        return str.replaceAll("['\"]", "");
    }


    private String findPrimaryKey(String tableName) {
        Table table = dataDictionary.get(tableName);
        if (table == null) return "";
        for (Column col : table.getColumns()) {
            if (col.isPrimaryKey()) {
                return col.getName();
            }
        }
        return "";
    }


    private Set<String> getMatchPks(String tableName, String condition) {
        Set<String> matchPks = new HashSet<>();
        Map<String, Map<String, String>> colDataMap = tableData.get(tableName);
        if (colDataMap == null) return matchPks;

        Matcher condMatcher = PATTERN_CONDITION.matcher(condition);
        if (!condMatcher.find()) {
            System.err.println("[ERROR] 条件格式错误！示例：age>20 或 name='张三'");
            return matchPks;
        }

        String colName = condMatcher.group(1).trim();
        String op = condMatcher.group(2).trim();
        String targetVal = removeQuotes(condMatcher.group(3).trim());

        if (!colDataMap.containsKey(colName)) {
            System.err.println("[ERROR] 列'" + colName + "'不存在");
            return matchPks;
        }

        Map<String, String> colVals = colDataMap.get(colName);
        for (Map.Entry<String, String> entry : colVals.entrySet()) {
            String pk = entry.getKey();
            String val = entry.getValue();

            if (isInt(val) && isInt(targetVal)) {
                int v = Integer.parseInt(val);
                int tv = Integer.parseInt(targetVal);
                if (operators.getOrDefault(op, (a, b) -> false).test(v, tv)) {
                    matchPks.add(pk);
                }
            } else {
                if ("=".equals(op) && val.equals(targetVal)) {
                    matchPks.add(pk);
                } else if ("!=".equals(op) && !val.equals(targetVal)) {
                    matchPks.add(pk);
                }
            }
        }

        return matchPks;
    }


    private boolean isInt(String str) {
        try {
            Integer.parseInt(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }


    public void clearMemory() {
        tableData.clear();
    }


    public Map<String, Table> getDataDictionary() {
        return dataDictionary;
    }
}